package com.foosun.core.base;

import com.foosun.core.util.JDBCName;
import com.foosun.core.util.ReflectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class BaseDao<T extends BaseEntity> {

    private static Logger LOGGER = Logger.getLogger(BaseDao.class);

    private NamedParameterJdbcTemplate jdbcTemplate;

    protected Class<T> entityClass;
    protected String tableName = null;

    /**
     * 构造方法
     */
    public BaseDao() {
        this.entityClass = ReflectionUtils.getSuperClassGenricType(getClass());
    }

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public NamedParameterJdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    /**
     * 查询所有的结果
     *
     * @return
     */
    public List<T> findAll() throws Exception {
        LOGGER.info(entityClass.getName());
        List<T> list = null;
        try {
            String sql = "select * from `" + getTableName() + "`";
            LOGGER.info(sql);
            list = jdbcTemplate.getJdbcOperations().query(sql, getRowMapper());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
        return list;
    }

    public Map findMax(String property) throws Exception {
        LOGGER.info(entityClass.getName());
        Map entity = null;
        try {
            String sql = "select MAX(`" + property + "`) max,id from `" + getTableName() + "`";
            LOGGER.info(sql);
            entity = (Map) jdbcTemplate.getJdbcOperations().queryForObject(sql, new BeanPropertyRowMapper() {
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map map = new HashMap();
                    map.put("id", rs.getInt("id"));
                    map.put("max", rs.getObject("max"));
                    return map;
                }
            });
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }

        return entity;
    }

    public Map findMaxByGroup(String property, String gName, Object gValue) throws Exception {
        LOGGER.info(entityClass.getName());
        Map entity = null;
        try {
            String sql = "select MAX(`" + property + "`) max,id from `" + getTableName() + "` WHERE `" + gName + "` = ?";
            LOGGER.info(sql);
            entity = (Map) jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{gValue}, new BeanPropertyRowMapper() {
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map map = new HashMap();
                    map.put("id", rs.getInt("id"));
                    map.put("max", rs.getObject("max"));
                    return map;
                }
            });
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }

        return entity;
    }

    public T findMaxOneByGroup(String property, String gName, Object gValue) throws Exception {
        LOGGER.info(entityClass.getName());
        T entity = null;
        try {
            String sql = "select * from `" + getTableName() + "` A where A.`" + property + "` = (select MAX(B.`" + property + "`) max  from `" + getTableName() + "` B WHERE B.`" + gName + "` = ?)";
            LOGGER.info(sql);
            entity = jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{gValue}, new BeanPropertyRowMapper<T>(entityClass));
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }

        return entity;
    }

    public T findMaxOne(String property) throws Exception {
        LOGGER.info(entityClass.getName());
        T entity = null;
        try {
            String sql = "select * from `" + getTableName() + "` A where A.`id` = (select C.`id` from (select MAX(B.`" + property + "`) max,B.id  from `" + getTableName() + "` B) C)";
            LOGGER.info(sql);
            entity = jdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{}, new BeanPropertyRowMapper<T>(entityClass));
        } catch (Exception e) {
            LOGGER.error(e);
            //throw e;
        }

        return entity;
    }

    /**
     * 分页查询
     *
     * @param pageData
     * @return
     */
    public PageData<T> findList(PageData<T> pageData) throws Exception {
        LOGGER.info(entityClass.getName());
        List<T> list = null;
        try {

            Map paramMap = new HashMap();
            Map<String, Map> sqlTotalMap = pageData.getSqlTotalMap();
            String totalSql = "";
            if (sqlTotalMap != null) {
                if (sqlTotalMap.keySet().size() > 0) {
                    Iterator<String> iterator = sqlTotalMap.keySet().iterator();
                    while (iterator.hasNext()) {
                        String key = iterator.next();
                        totalSql += key + " AND ";
                        paramMap.putAll(sqlTotalMap.get(key));
                    }
                    totalSql = " WHERE " + totalSql.substring(0, totalSql.length() - 4);
                }
            }
            String sql = "select count(*) from `" + getTableName() + "`" + totalSql;
            if (pageData.getPagination().isReadTotalCount()) {
                Long total = jdbcTemplate.queryForLong(sql, paramMap);
                LOGGER.info(sql);
                pageData.getPagination().setTotalCount(total);
            }

            List<Object> plist = new ArrayList<Object>();
            Map<String, List<Object>> sqlMap = pageData.getSqlMap();
            String searchSQl = "";
            if (sqlMap != null) {
                if (sqlMap.keySet().size() > 0) {
                    Iterator<String> iterator = sqlMap.keySet().iterator();
                    while (iterator.hasNext()) {
                        String key = iterator.next();
                        searchSQl += key + " AND ";
                        plist.addAll(sqlMap.get(key));
                    }
                    searchSQl = " WHERE " + searchSQl.substring(0, searchSQl.length() - 4);
                }
            }


            long start = pageData.getPagination().getCurrentlyPageFirstResoultIndex();
            long num = pageData.getPagination().getPageSize();

            String orderSql = " ORDER BY `insert_time` DESC";
            if (StringUtils.isNotBlank(pageData.getOrderName()) && StringUtils.isNotBlank(pageData.getAscDesc())) {
                orderSql = " ORDER BY `" + pageData.getOrderName() + "` " + pageData.getAscDesc();
            }

            sql = "select * from `" + getTableName() + "`" + searchSQl + orderSql + " limit ?,?";
            plist.add(start);
            plist.add(num);
            list = jdbcTemplate.getJdbcOperations().query(sql, plist.toArray(), getRowMapper());
            pageData.setResult(list);
            LOGGER.info(sql);
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
        return pageData;
    }


    @SuppressWarnings({"rawtypes", "unchecked"})
    public PageData findBySql(PageData pageData, String sql, ResultSetExtractor<List> resultSetExtractor) {
        Map paramMap = new HashMap();
        Map<String, Map> sqlTotalMap = pageData.getSqlTotalMap();
        List<Object> plist = new ArrayList<Object>();
        StringBuffer sqlBuffer = new StringBuffer("select count(*) from (").append(sql);
        if (sqlTotalMap != null) {
            if (sqlTotalMap.keySet().size() > 0) {
                Iterator<String> iterator = sqlTotalMap.keySet().iterator();
                while (iterator.hasNext()) {
                    String key = iterator.next();
                    sqlBuffer.append(" AND ").append(key);
                    paramMap.putAll(sqlTotalMap.get(key));
                }
            }
        }
        sqlBuffer.append(") T");
        if (pageData.getPagination().isReadTotalCount()) {
            Long total = jdbcTemplate.queryForLong(sqlBuffer.toString(), paramMap);
            LOGGER.info(sqlBuffer);
            pageData.getPagination().setTotalCount(total);
        }


        long start = pageData.getPagination().getCurrentlyPageFirstResoultIndex();
        long num = pageData.getPagination().getPageSize();

        sqlBuffer = new StringBuffer("select * from (").append(sql);
        Map<String, List<Object>> sqlMap = pageData.getSqlMap();
        if (sqlMap != null) {
            if (sqlMap.keySet().size() > 0) {
                Iterator<String> iterator = sqlMap.keySet().iterator();
                while (iterator.hasNext()) {
                    String key = iterator.next();
                    sqlBuffer.append(" AND ").append(key);
                    plist.addAll(sqlMap.get(key));
                }
            }
        }
        plist.add(start);
        plist.add(num);
        if (StringUtils.isNotBlank(pageData.getOrderName()) && StringUtils.isNotBlank(pageData.getAscDesc())) {
            sqlBuffer.append(" ORDER BY ").append(pageData.getOrderName()).append(" ").append(pageData.getAscDesc());
        } else {
            sqlBuffer.append(" ORDER BY `insert_time` DESC ");
        }
        sqlBuffer.append(") T limit ?,?");

        pageData.setResult(jdbcTemplate.getJdbcOperations().query(sqlBuffer.toString(), plist.toArray(), resultSetExtractor));
        return pageData;

    }

    public void delete(T entity) throws Exception {
        this.delete(entity.getId());
    }

    public void delete(Integer id) throws Exception {
        try {
            String sql = "delete from " + getTableName() + " where id = " + id;
            jdbcTemplate.getJdbcOperations().execute(sql);
            LOGGER.info(sql);
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
    }

    public void deleteAll() throws Exception {
        try {
            String sql = "delete from `" + getTableName() + "`";
            jdbcTemplate.getJdbcOperations().execute(sql);
            LOGGER.info(sql);
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
    }

    public List<T> findAll(int topNum) throws Exception {
        LOGGER.info(entityClass.getName());
        List<T> list = null;
        try {
            String sql = "select * from " + getTableName() + " order by insert_time desc limit 0," + topNum;
            LOGGER.info(sql);
            list = jdbcTemplate.getJdbcOperations().query(sql, getRowMapper());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
        return list;
    }

    public int countByExample(T entity) throws Exception {
        LOGGER.info(entityClass.getName());
        try {
            String sql = "";
            Map<String, Object> map = entity.getEntityMap();
            map.put("insert_time", null);
            map.put("last_update_time", null);
            StringBuffer where = new StringBuffer();
            Iterator<String> keys = map.keySet().iterator();
            List<Object> params = new ArrayList<Object>();
            while (keys.hasNext()) {
                String key = keys.next();
                if (map.get(key) != null) {
                    where.append("and `").append(key).append("`= ? ");
//                    where += "and `" + key + "`= ? ";
                    params.add(map.get(key));
                }
            }
            if (where.length() > 3) {
                where = new StringBuffer(where.toString().substring(3));
            }

            sql = "select count(*) from `" + getTableName() + "` where " + where.toString();

            return jdbcTemplate.getJdbcOperations().queryForInt(sql, params.toArray());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
    }

    public List<T> findBySql(String sql, Object[] params) throws Exception {
        LOGGER.info(entityClass.getName());
        List<T> list = null;
        try {
            list = jdbcTemplate.getJdbcOperations().query(sql, params, getRowMapper());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
        return list;
    }

    public List<T> findByIds(List<Integer> idList) throws Exception {
        LOGGER.info(entityClass.getName());

        if (idList == null || idList.isEmpty()) {
            return new ArrayList<T>();
        }

        List<T> list = null;

        try {
            Object[] prams = new Object[idList.size()];
            String ids = "";
            int i = 0;
            for (Integer id : idList) {
                ids += " `id`=? or ";
                prams[i] = id;
                i++;
            }
            ids = ids.trim();
            ids = ids.substring(0, ids.length() - 2);
            String sql = "select * from `" + getTableName() + "` where " + ids;

            list = jdbcTemplate.getJdbcOperations().query(sql, prams, getRowMapper());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
        return list;

    }

    public List<T> findByProperty(String property, Object value) throws Exception {
        LOGGER.info(entityClass.getName());
        List<T> list = null;
        try {
            String sql = "select * from `" + getTableName() + "` where `" + property + "` = ?";
            Object[] prams = {value};

            list = jdbcTemplate.getJdbcOperations().query(sql, prams, getRowMapper());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
        return list;
    }

    /**
     * 根据实体对象的某些属性查找实体对象列表，若查找不到，则返回的list size 为 0.
     *
     * @param property 属性数组
     * @param value    属性对应的值的数组
     * @return
     * @throws Exception
     */
    public List<T> findByProperty(String[] property, Object[] value) throws Exception {
        LOGGER.info(entityClass.getName());
        List<T> list = new ArrayList<T>();
        if (property != null && value != null && property.length == value.length) {
            try {
                StringBuffer sql = new StringBuffer("select * from `" + getTableName() + "` where 1=1 ");
                for (int i = 0; i < property.length; i++) {
                    sql.append(" and `").append(property[i]).append("` = ? ");
                }
                list = jdbcTemplate.getJdbcOperations().query(sql.toString(), value, getRowMapper());
            } catch (Exception e) {
                LOGGER.error(e);
                throw e;
            }
        } else {
            throw new NullPointerException("请核对传入的参数");
        }
        return list;
    }

    public List<T> findByProperty(String property, Object value, int topNum) throws Exception {
        LOGGER.info(entityClass.getName());
        List<T> list = null;
        try {
            String sql = "select * from `" + getTableName() + "` where `" + property + "` = ? order by `insert_time` desc limit 0," + topNum;
            Object[] prams = {value};

            list = jdbcTemplate.getJdbcOperations().query(sql, prams, getRowMapper());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
        return list;
    }

    public T findByPropertyOne(String property, Object value) throws Exception {
        List<T> list = findByProperty(property, value);
        return list == null || list.isEmpty() ? null : list.get(0);
    }

    public void updateByID(Integer id, String property, Object value) throws Exception {
        try {
            String sql = "update " + this.getTableName() + " set " + property + " = ? where id = ?";
            int r = jdbcTemplate.getJdbcOperations().update(sql, value, id);
            LOGGER.info("update :" + r);
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
    }

    public T findById(Integer id) throws Exception {
        List<T> list = findByProperty("id", id);
        return list == null || list.isEmpty() ? null : list.get(0);
    }

    public String getTableName() {
        if (tableName == null) {
            tableName = entityClass.getAnnotation(JDBCName.class).value();
        }
        return tableName.toLowerCase();
    }

    private RowMapperResultSetExtractor<T> getRowMapper() {
        return new RowMapperResultSetExtractor<T>(new BeanPropertyRowMapper<T>(entityClass));
    }

    public void save(T entity) throws Exception {
        try {
            Map<String, Object> map = entity.getEntityMap();
            String cols = "";
            String vals = "";
            Iterator<String> keys = map.keySet().iterator();
            List<Object> params = new ArrayList<Object>();
            while (keys.hasNext()) {
                String key = keys.next();
                if (map.get(key) != null) {
                    cols += "`" + key + "`,";
                    params.add(map.get(key));
                    vals += "?,";
                }

            }
            vals = vals.substring(0, vals.lastIndexOf(","));
            cols = cols.substring(0, cols.lastIndexOf(","));
            String sql = "insert into " + getTableName() + " (" + cols + ") values (" + vals + ")";
            LOGGER.info(sql);
            this.jdbcTemplate.getJdbcOperations().update(sql, params.toArray());
            String maxId = "select max(`id`) from " + getTableName();
            Integer id = this.jdbcTemplate.queryForInt(maxId, new HashMap<String, Object>());
            entity.setId(id);
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
    }

    public void update(T entity) throws Exception {
        try {
            Map<String, Object> map = entity.getEntityMap();
            map.put("insert_time", null);
            String sets = "";
            Iterator<String> keys = map.keySet().iterator();
            List<Object> params = new ArrayList<Object>();
            while (keys.hasNext()) {
                String key = keys.next();
                if (map.get(key) != null) {
                    sets += "`" + key + "`= ?,";
                    params.add(map.get(key));
                }

            }
            sets = sets.substring(0, sets.lastIndexOf(","));
            String sql = "update `" + getTableName() + "` set " + sets + " where id = ?";
            params.add(entity.getId());
            LOGGER.info("update:" + sql);
            this.jdbcTemplate.getJdbcOperations().update(sql, params.toArray());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
    }


    public List<T> findByExample(T entity) throws Exception {
        LOGGER.info(entityClass.getName());
        List<T> list = null;
        try {

            Map<String, Object> map = entity.getEntityMap();
            map.put("insert_time", null);
            map.put("last_update_time", null);
            String where = "";
            Iterator<String> keys = map.keySet().iterator();
            List<Object> params = new ArrayList<Object>();
            while (keys.hasNext()) {
                String key = keys.next();
                if (map.get(key) != null) {
                    where += "and `" + key + "`= ? ";
                    params.add(map.get(key));
                }
            }
            where = where.substring(3);

            String sql = "select * from `" + getTableName() + "` where " + where;

            list = jdbcTemplate.getJdbcOperations().query(sql, params.toArray(), getRowMapper());
        } catch (Exception e) {
            LOGGER.error(e);
            throw e;
        }
        return list;

    }
}
